# 1-导包
import pymysql
from pymysql.constants import CLIENT
from api import log
import traceback


# 2-定义一个数据库执行SQL语句的方法
def execute_sql(sql):

    # 初始化定义变量
    cursor = None
    conn = None

    try:
        # 2.1 创建1个数据库连接
        conn = pymysql.connect(host="121.43.169.97", user="student", port=3306, password="P2P_student_2022",
                               database="czbk_member", charset="utf8", autocommit=True, client_flag=CLIENT.MULTI_STATEMENTS)
                                # autocommit实现自动提交，client_flag实现的批量执行多条SQL语句
        # 2.2-获取数据库连接的游标
        cursor = conn.cursor()
        # 2.3-通过游标执行sql语句
        cursor.execute(sql)

        # 判断SQL语句执行的类型，返回执行的结果, lower()将字符串全部转为小写
        if "select" in sql.lower():
            log.info("执行SQL语句成功，查询结果为：{}".format(cursor.fetchone()))  # 获取查询结果
        else:
            log.info("执行SQL语句成功，修改结果为：{}".format(cursor.rowcount)) # 获取增删改被影响的行数
    except Exception as e:
        # 记录异常信息
        log.error("执行SQL语句出现异常，快来瞅一下：{}".format(traceback.format_exc()))
        # 出现异常的时候执行回滚
        conn.rollback()
        # 抛出异常
        raise
    finally:
        # 2.4 关闭游标
        cursor.close()
        # 2.5 关闭连接
        conn.close()


# 封装执行数据构造和清理方法
def clear_data():
    # 1-用户数据清理
    sql1= """
    delete i.* from mb_member_info i INNER JOIN mb_member m on i.member_id=m.id where m.phone in ("13800000001","13800000002","13800000003","13800000004");
    delete l.* from mb_member_login_log l INNER JOIN mb_member m on l.member_id=m.id where m.phone in ("13800000001","13800000002","13800000003","13800000004");
    delete from mb_member_register_log where phone in ("13800000001","13800000002","13800000003","13800000004");
    delete from mb_member where phone in ("13800000001","13800000002","13800000003","13800000004");
    """
    # 执行SQL语句
    execute_sql(sql1)
    # 2-借款标数据清理和构造
    sql2 = """
    -- 清空操作
delete from `czbk_member`.`mb_member` where id=869;
delete from `czbk_member`.`mb_member_info` where id=987;
delete from `czbk_finance`.`fn_loan_amount` where id=987;
delete from `p2p_mock`.`p2p_account` where id=911;
delete from `czbk_finance`.`fn_loan` where id=642;
delete from `czbk_finance`.`fn_loan_info` where id=832;
delete from `czbk_finance`.`fn_loan_amount_log` where id=869;

-- 借款人构造
INSERT INTO `czbk_member`.`mb_member` (`id`, `name`, `trust_account`, `password`,
`email`, `phone`, `paypassword`, `count`, `register_ip`, `register_time`,
`register_date`, `lastlogin_ip`, `lastlogin_time`, `lastlogin_date`,
`register_type`, `role`, `group`, `group_status`, `is_realname`, `is_email`,
`is_phone`, `is_video`, `is_id5`, `is_vip`, `credit_point`, `is_auto`,
`pwd_attach`, `status`, `lock_time`, `vip_start_time`, `vip_end_time`,
`vip_category_id`, `vip_category_ind`, `amount`, `phonestr`) VALUES ('869',
'13099775533', '6021234507160403', 'eab70f4a46c640b3dfc35040bf37c803', NULL,
'13099775533', NULL, '1', '1972826569', '1588835827', '2020-05-07', '1972826569',
'1588835827', '2020-05-07', '1', '1', '1', '2', '1', '-1', '1', '-1', '-1', '-1',
'0', '-1', '1m4U82PqPx', '1', NULL, NULL, NULL, NULL, NULL, '0.00', NULL);
INSERT INTO `czbk_member`.`mb_member_info` (`id`, `member_id`, `member_name`,
`avatar`, `nickname`, `realname`, `gender`, `birthday`, `card_id`, `qq`,
`educational_background`, `marry_status`, `graduated`, `company_industry`,
`company_scale`, `company_office`, `monthly_income`, `invite_money`,
`hometown_province`, `hometown_city`, `hometown_area`, `hometown_post`) VALUES
('987', '869', '13099775533', NULL, NULL, '⼩⽯头', NULL, '1986-11-18',
'429009118611181111', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL);
INSERT INTO `czbk_finance`.`fn_loan_amount` (`id`, `member_id`, `member_name`,
`credit_amount`, `credit_amount_freeze`, `vouch_amount`, `vouch_amount_freeze`,
`pawn_amount`, `pawn_amount_freeze`, `roam_amount`, `roam_amount_freeze`) VALUES
('987', '869', '13099775533', '200000.000000', '0.000000', '0.000000',
'0.000000', '0.000000', '0.000000', '0.000000', '0.000000');
INSERT INTO `p2p_mock`.`p2p_account` (`id`, `account`, `AvlBal`, `AcctBal`,
`FrzBal`, `create_time`) VALUES ('911', '6021234507160403', '0.00', '0.00',
'0.00', '2020-05-07 16:04:03');
-- 借款标构造
INSERT INTO `czbk_finance`.`fn_loan` (`id`, `ind`, `serialno`, `name`,
`member_id`, `member_name`, `amount`, `credited_amount`, `progress`,
`tender_count`, `category_id`, `category_type`, `repay_type`, `period`, `apr`,
`sort_index`, `sort_top`, `status`, `hidden_status`, `additional_status`,
`deposit_certificate`, `certificate_file_id`, `loan_repay_status`,
`loan_repay_time`, `overdue_time`, `add_date`, `add_time`, `verify_time`,
`reverify_time`, `add_ip`, `vouch_company_id`, `op_status`, `marker_type`) VALUES
('642', 'f5380d59791773a8abad4141ff90a5a4', '202008231111', '数据库构造借款1',
'869', '13099775533', '100000.00', '0.00', '0.00', '0', '1', '1', '1', '60',
'7.55', '1', NULL, '3', '1', '-1', '-1', NULL, NULL, NULL, '1609501013', 
'2020-05-07', '1588847800', '1588851413', NULL, '1972826569', '0', '-1', '数据库');
INSERT INTO `czbk_finance`.`fn_loan_info` (`id`, `loan_id`, `thumbs`, `contents`,
`attachment_ids`, `password`, `use`, `tender_amount_min`, `tender_amount_max`,
`freeze_amount`, `freeze_amount_proportion`, `freeze_period`, `award_status`,
`fail_award_status`, `award_amount`, `award_proportion`, `award_amount_total`,
`validate`, `part_status`, `tender_count`, `comment_status`, `comment_count`,
`is_company`, `company_name`, `vouch_company_info`, `vouch_company_pic`,
`vouch_company_guaranty`, `amount_category_id`, `hits`, `cancel_admin_id`,
`cancel_remark`, `cancel_time`, `verify_admin_id`, `verify_admin_name`,
`verify_remark`, `verify_time`, `verify_ip`, `reverify_admin_id`,
`reverify_admin_name`, `reverify_remark`, `reverify_time`, `reverify_ip`,
`auto_scale`, `is_auto`, `additional_status`, `additional_apr`,
`additional_name`, `additional_amount_max`, `additional_pic`, `product_process`,
`information`) VALUES ('832', '642', NULL, '数据库构造借款1', 'a:0:{}', NULL,
'10102', '100.00', '1000.00', NULL, '10.00', NULL, '-1', '-1', NULL, NULL, NULL,
'239', NULL, '0', '-1', '0', '-1', NULL, NULL, NULL, NULL, '1', '1', NULL, NULL,
NULL, '4', 'admin', '数据库构造借款1 通过', '1588851413', '1972826569', NULL, NULL,
NULL, NULL, NULL, NULL, '1', '-1', '0.000000', NULL, '0.000000', NULL, NULL,
NULL);
UPDATE `czbk_finance`.`fn_loan_amount` SET `credit_amount_freeze`='100000.000000'
WHERE `member_id`='869';
INSERT INTO `czbk_finance`.`fn_loan_amount_log` (`member_id`, `member_name`,
`category_id`, `type`, `amount`, `remark`,
`add_time`) VALUES ('869','13099775533', 1, 4, '100000', '借款冻结',
'1972826569');
    """
    # 执行SQL语句
    execute_sql(sql2)


# 测试数据清理方法
clear_data()